import pandas as pd
import os
import re
from sqlalchemy import create_engine, inspect, text, exc
from sqlalchemy.types import VARCHAR, Integer, Float, DateTime

# ======== 需修改的配置区域 ========
# 根据您的图片目录结构调整路径
EXCEL_PATH = "../Extractor/data/database/Drops.xlsx"  # 使用相对路径，正确指向Excel文件

# 使用database.yaml配置文件更安全（建议）
# DB_CONN_STR = "mysql+pymysql://root:123456@localhost:3306/py"  # 临时连接字符串

# 改为从database.yaml读取配置
with open("database.yaml") as f:  # 配置文件与脚本在同一目录
    import yaml

    db_config = yaml.safe_load(f)
DB_CONN_STR = (
    f"mysql+pymysql://{db_config['user']}:{db_config['password']}"
    f"@{db_config['host']}:{db_config['port']}/{db_config['dbname']}"
)


# ================================

def excel_to_database(excel_path, db_conn_str):
    """读取Excel并写入数据库，自动根据文件名建表"""
    try:
        # 1. 读取Excel文件
        df = pd.read_excel(excel_path)
        print("✅ Excel读取成功，数据维度：", df.shape)

        # 2. 根据文件名生成表名（移除扩展名）
        table_name = os.path.splitext(os.path.basename(excel_path))[0]
        # 清理非法字符（保留字母数字下划线）并转为小写
        table_name = ''.join(filter(str.isalnum, table_name)).lower()
        print(f"生成的表名: {table_name}")

        # 3. 连接数据库
        engine = create_engine(db_conn_str)
        inspector = inspect(engine)

        # 4. 清理列名（将非字母、数字、下划线转换为下划线）
        df.columns = [re.sub(r'[^a-zA-Z0-9_]', '_', col) for col in df.columns]
        print(f"清理后的列名: {list(df.columns)}")

        # 5. 动态建表（若表不存在）
        if not inspector.has_table(table_name):
            # 数据类型映射
            type_map = {
                "int64": "INTEGER",
                "float64": "FLOAT",
                "datetime64": "DATETIME",
                "object": "VARCHAR(255)"
            }

            # 生成列定义
            col_defs = []
            for col in df.columns:
                dtype = type_map.get(str(df[col].dtype), "VARCHAR(255)")
                col_defs.append(f"`{col}` {dtype}")

            create_sql = f"CREATE TABLE `{table_name}` ({', '.join(col_defs)})"
            print(f"建表SQL: {create_sql}")

            # 使用text()包装SQL并执行
            with engine.begin() as conn:
                conn.execute(text(create_sql))
            print(f"🆕 表已创建：{table_name}")

        # 6. 写入数据（使用pandas的内置方法）
        # 解决SQLAlchemy执行问题的方法
        try:
            df.to_sql(
                name=table_name,
                con=engine,
                if_exists="append",  # 追加数据到现有表
                index=False,  # 不写入DataFrame索引
                method="multi",  # 批量插入提高性能
                chunksize=1000  # 分批处理大数据量
            )
            print(f"💾 数据已写入表：{table_name}，写入行数：{len(df)}")
        except exc.ProgrammingError as pe:
            # 处理可能的类型不匹配错误
            print(f"⚠️ 类型不匹配错误: {pe}")
            # 尝试使用VARCHAR(255)重新写入
            dtype = {col: VARCHAR(255) for col in df.columns}
            df.to_sql(
                name=table_name,
                con=engine,
                if_exists="append",
                index=False,
                dtype=dtype
            )
            print(f"💾 (降级处理)数据已写入表：{table_name}")

    except exc.DBAPIError as dbe:
        print(f"❌ 数据库错误: {dbe}")
    except Exception as e:
        print(f"❌ 发生错误：{e}")
        raise


if __name__ == "__main__":
    excel_to_database(EXCEL_PATH, DB_CONN_STR)